package BeenChina.PM.Util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.NumberFormat;
import java.text.ParsePosition;

import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem; 
import org.apache.poi.ss.util.CellRangeAddress;

public class ExportToExcel {	
	public static void export(String file, Map<String, JSONArray> dataset, List<ExportInfo> info,OutputStream out) throws IOException {
		FileInputStream fileinput = new FileInputStream(file);
		POIFSFileSystem fs = new POIFSFileSystem(fileinput);
		HSSFWorkbook workbook = new HSSFWorkbook(fs);
		
		for (int n=0;n<info.size();n++){
			HSSFSheet sheet = workbook.getSheet(info.get(n).getSheetName());
			if(sheet==null){
				sheet=workbook.createSheet(info.get(n).getSheetName());
			}
			JSONArray  table  = dataset.get(info.get(n).getGridName());
			if(table==null){
				continue;
			}
			
			int curRow=info.get(n).getStartRow();
			int curCol = info.get(n).getStartCol();

			for(int i=0;i<table.size();i++,curRow++,curCol=info.get(n).getStartCol()){
				JSONArray rowData = table.getJSONArray(i);
				if(rowData == null){
	                continue;
				}
				HSSFRow row = sheet.getRow(curRow);
				if(row==null){
				  	row=sheet.createRow(curRow);
				}
				for(int j=0;j<rowData.size();j++){
					Object data = rowData.get(j);
					HSSFCell cell = row.getCell(curCol);
					if(cell==null){
						cell = row.createCell(curCol);
					}
					CellRangeAddress cellRange=getMergedRegion(sheet,curRow,curCol);
					if(cellRange.getFirstColumn()==curCol)
					{
						String s = data.toString();					
					if( isNumeric(s) ){
						double value = Double.parseDouble(s); 
						cell.setCellValue(value);
					}
					else{
						s = s.replace("<br>", "");
						cell.setCellValue(s);
					}
				}
					curCol =curCol + (cellRange.getLastColumn()-cellRange.getFirstColumn())+1;
			}
		}
		}		
		workbook.write(out);
		out.flush();
	}	
	
  	public static boolean isNumeric(String str){
  		if (str == null || str.trim().equals(""))
  			return false;
  		NumberFormat formatter = NumberFormat.getInstance();
    	ParsePosition pos = new ParsePosition(0);
    	formatter.parse(str, pos);
    	return str.length() == pos.getIndex();
  	}
  	
  	public static CellRangeAddress getMergedRegion(HSSFSheet sheet , int row , int column){  
  	    int sheetMergeCount = sheet.getNumMergedRegions();  
  	    
  	    for(int i = 0 ; i < sheetMergeCount ; i++ ){  
  	        CellRangeAddress ca = sheet.getMergedRegion(i);    	       
  	        int firstColumn = ca.getFirstColumn();  
  	        int lastColumn = ca.getLastColumn();    	        
  	        int firstRow = ca.getFirstRow();  
  	        int lastRow = ca.getLastRow();    	          
  	        if(row >= firstRow && row <= lastRow){  
  	            if(column >= firstColumn && column <= lastColumn){    	                  
  	                return ca ;  
}
  	        }
  	    }
  	  CellRangeAddress ca = new CellRangeAddress(row,row,column,column);
  	  return ca;  	    	    
  	}
}
